Transact——SQL

---

一、基础概念

Transact-SQL就是在标准SQL的基础上进行扩充而推出的SQL Server专用的结构化SQL，引入了程序设计的思想，增强了程序的流程控制语句等。

---

二、变量

全局变量：全局变量是由系统定义和维护的，用户只能使用预先说明及定义的全局变量。因此，全局变量对于用户来说是只读的。
使用全局变量时必须以@@开头。

局部变量：局部变量是由用户自定义的变量，其名称命名规则同标识符的命名规则，不区分大小写。

注释符：

• --：注释一行。

• /**/：注释全部。

使用

# 1.声明（局部变量必须先声明才可以使用）
declare @var type [, @var type , ...]


# 2.赋值
-- 可以多行赋值，用,隔开
select @var = value [, @var = value  , ...]
-- 不可以多行赋值
set @var = value


# 3.输出
print @var # print输出是文本输出，而不是一列

案例

declare @id char(8)
select @id = '10010001'

---

三、运算符

算术运算符：+、-、*、/、%

赋值运算符：=

字符串连接运算符："This is :" + " apple." = "This is : apple."

比较运算符：=、>、<、>=、<=、<>（不等于）） （后面为非SQL-92标准）!=、!<、!>

逻辑运算符：all and any between exists in like not or some（一系列操作数中有些值为true结果为true）

按位运算符：&、|、^

一元运算符：+、-（ 正号负号）、~（取反）

---

四、批处理

1 go语句

语句

特点：一个批处理内的所有语句要么放在一起通过解析，要么没有一句能执行。

SQL Server中go语句使用注意事项：

1. GO 命令和 Transact-SQL 语句不能在同一行中.但在 GO 命令行中可包含注释。
2. 用户必须遵照使用批处理的规则.例如,在同一批处理中,创建数据库之后不能直接使用其新建的数据库。
3. 局部（用户定义）变量的作用域限制在一个批处理中,不可在 GO 命令后引用。
4. 每个被GO分隔的语句都是一个单独的事务，一个语句执行失败不会影响其它语句执行。
5. GO [count] count 为正整数.指定GO 之前的批处理执行指定的次数。

语法：

​statement

​go

案例

create database [Roc]
GO
use [Roc]
GO

---

五、流程控制语句

1 begin…end

begin
<statement>
end
# 用途：begin...end用来设定一个程序块，将在begin...end中的所有程序视为一个单元执行。

2 if…else

if <exp>  
<statement> 
[else if <exp>
<statement> ]
[else
<statement> ]

3 if [not] exists

if [not] exists (select 子查询)
<statement> 
[else
<statement> ]
# if exists 语句用于检测数据是否存在

4 case

# 用法1：
case <exp>
    when <exp> then <exp>
    ...
    when <exp> then <exp>
    [ else <exp> ]
end
# 该语句是将case后面表达式与when子句中的表达式的值进行比较，如果相等返回then并跳出case。当case中没有else时全部失败返回null。

# 用法2：
case
    when <exp> then <exp>
    ... 
   when <exp> then <exp>
   [ else <exp> ]
end
# 该语句是若when为真则返回then并跳出case。当case中没有else时全部失败返回null。

# case命令可以嵌套到SQL命令中
sex=
case ... end

5 while…continue…break

while <exp>
begin
    <statement>
    [ break ]
    [ continue ]
    [statement]
end

6 waitfor

7 goto

# 相当于jmp，先设定标识符，再jmp至标识符处
goto 标识符

8 return

return ([整数值])

RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。

return 不指定返回 0

return 不能返回null

return语句返回值

---

六、常用命令

1 backup

用处：备份。

2 restore

用处：备份。

3 checkpoint

用处：将当前工作的数据库中被更改过的数据页或日志页从数据缓冲区中强制写入硬盘。

用法：checkpoint [ checkpoint_duration ]

参数为int且必须大于0，单位为秒，表示SQL Server数据库引擎会在请求的持续时间内尝试执行检查点。

省略则SQL Server数据库引擎会自动调整检查点时间。

4 DBCC

用处：验证数据库完整性，查找错误，分析系统使用等等。

dbcc 子命令
decc help ( '?' ) # 可以查询DBCC使用的所有命令。
dbcc help ( '子命令' ) # 可以查指定命令的用法。

5 declare

用处：声明局部变量，游标变量，表变量。

定义表

declare {{ @ lcoal_variable data_type }
| { @ cursor_variable_name CURSOR }
| { table_type_definition }
} [ , ... n ]
# 不能声明 text，ntext，image类型。

# 表定义：
declare @order table(ID int,Name varchar(20))
declare @Detail table(ID int,Price float)
insert into @order(ID,Name) values(1,'aa'),(2,'bb'),(3,'cc')
insert into @Detail(ID,Price) values(1,10.5),(2,12.3),(4,5.9)
select * from @order
select * from @Detail

6 execute

execute 或 exec 执行存储过程。

7 kill

kill 命令用于终止某一过程的执行。

8 print

print 'any ASCII text' | @ local_variable | @@ FUNCTION | string_expression

# print可以用 ' + ' 拼接多个变量输出在同一行。
print @x + @y 

9 raiserror

用于在SQL Server系统返回错误信息时返回用户指定信息。

10 select

用于变量赋值。

select { @local_variable = expression } [ , ... n ]

11 set

# 1、局部变量赋值
set { { @local_variable = expression } | { @cursor_variable =
            { @cursor_variable1 cursor_name
                   | { cursor
                   [ forward_only | scroll ]
                   [ static | keyset | dynamic | optimistic ]
                   [ read_only | scroll_lcoks | optimistic ]
                   [ type_warning ]
                   for select_statement
                   [ for { read only
                   | update [ of column_name [ , ... n ] } ]            
}}}}

# 2、用于用户执行SQL命令时，SQL Server处理选项的设定。
SET：ON
SET：OFF
SET：选项值

12 shutdown

停止SQL Server执行。

shutdown [ with nowait ]

带参数：立刻停止SQL Server，终止所有的用户过程并对每一个现行的事务发生一个回滚后退出SQL Server。

不带参数：

1. 阻止任何用户登入SQL Server。
2. 等待尚未完成的Transact-SQL命令或存储过程执行完毕。
3. 在每个数据库中执行checkpoint命令。
4. 停止SQL Server执行。

13 use

用处：切换使用数据库。（需要有权限）

use { database }

---

七、常用函数

1 统计

-- 返回表达式中所有数据的标准差
-- 表达式中NULL会被忽略，数据类型为NUMERIC或类似的列，返回float类型
stdev(exp)

-- 返回表达式中所有数据的总体标准差，其他同上
stdevp(exp)

-- 返回表达式中所有数据的统计变异数。
var(exp)

-- 返回表达式中所有数据的总体变异数。
varp(exp)

2 算术?redirectedfrom=MSDN)

3 字符串

# 字符串转换
-- ascii->int 输出
ascii( character_expression )

-- int- > ascii 输出
char( integer_expression )

-- str 全小写
lower ( integer_expression )

-- str 全大写
upper ( integer_expression )

-- int->str ()
-- length：返回字符串长度。
-- decimal：返回小数位数。
-- decimal大于length返回length个 *
str( float  _expression [ , length [ , < decimal >]] )


# 去空格函数
-- 去左空格
ltrim ( character_expression )

-- 去右空格
rtrim ( character_expression )


# 取子串函数
-- 取最左边到第 integer_expression 个字符的部分
left ( character_expression , integer_expression )

-- 取最右边到第 integer_expression 个字符的部分
right ( character_expression , integer_expression )

-- 从第 starting_position 个位置取 length 个字符
substring ( expression , starting_position , length )


# 字符串比较函数（两者区别：patindex子串中可以使用通配符，可以用于char、varchar、text数据类型）
-- 指定子串出现的开始位置。
-- substring_expression是要找的字符串
charindex ( substring_expression , expression )

-- 指定子串出现的开始位置。
-- substring_expression是要找的字符串。（两端必须带%号）
patindex ( ' %substring_expression% '  , expression )


# 字符串操作函数
-- 用符号括起字符串
-- quote_ character 默认是"[" 。（用[]括起）
quotename ( character_expression , [ , quote_ character ] )

-- 重复字符串
replicate ( character_expression , integer_expression )

-- 逆转字符串
reverse ( character_expression)

-- 替换字符串
-- 在 1 中找出 2 的子串，并用 3 替换 2 子串返回至 1 中
replace ( string_expression1 ,string_expression2 , string_expression3 )

-- 返回一个空格字符串
space( integer_expression )

-- 将字符串中某处的一定长度的字符串替换成目标字符串。
-- character_expression2：目标字符串
stuff( character_expression1 , start_position , length , character_expression2 )

4 数据类型转化

convert：时间转化

-- 类型转化
cast ( < expression > as < data_type > [ length ])

5 日期 链接

-- 返回日期
day ( < data_expression > )

-- 返回月份
month ( < data_expression > )

-- 返回年
year ( < data_expression > )

-- 日期增加
-- <datepart>：YY | M | D | WK | HH
dateadd ( < datepart > , < number > , < date > )

-- 日期差值
datediff ( < datepart > , < date1 > , < date2 > )

-- 字符串形式返回日期
datename ( < datepart > , < date > )

-- 整数形式返回日期
datepart ( < datepart > , < date > )

-- 获得当前日期和时间 datetime
getdate()

6 自定义

自定义函数

create function function_name
( [ { @parameter_name [ as ] parameter_data_type  [ = default ] [ readonly ]
[ , ... n ]
]
)
returns return_data_type
[ with encryption ]
[ as ]
begin
function_body
return scalar_expression
end 

-- function_name：函数名，同MySQL内置函数一样，大小写不敏感。
-- @parameter_name:：参数名，必须@ 开头，可以定义多个参数，逗号隔开。
-- parameter_data_type：参数类型。
-- [ = default ] ：默认值。
-- [ readonly ]：函数只读。
-- return_data_type：返回值类型。
-- with encryption：加密。任何人无法查看函数的定义。
-- begin...end：函数体。

---

八、存储过程

1 定义

2 创建

create procedure procedure_name [ ; number ]     -- number：用于区分同名存储过程
[ ( @parameter data_type }
[ varying ] [ = default ] [ output ]           -- varying output 游标 default 默认值 output 返回参数
] { , ... n }
[ with
    { recompile | encryption | recompile , encryption } ]  -- 每次都重新编译 | 加密
[ for replication ]   -- 没用
as sql_statement [ , ... n ]

3 查看

exec sp_helptext procedure_name

4 重命名

右键。

5 删除

drop procedure procedure_name

6 执行

exec procedure procedure_name

7 修改

其他同理create。

alter procedure procedure_name

8 特殊存储过程

sp_executesql：可以执行sql语句

exec sp_executesql @sql , @para , [ @para1 = @para1 , ... ]（SQL语句，SQL语句参数，参数值等于）

---

九、触发器

1 定义

2 创建

创建DML触发器

# 数据触发器

create trigger trigger_name 
ON table_name
[with encryption]  
for | after | instead of
[ delete | update | insert ] 
as sql_statement [ ; ]

# rollback transaction 回退数据库

创建DDL触发器

# 结构触发器
create trigger trigger_name
on { all server | database }
[ with encryption ]
{ for | after } { event_type | event_group } [ , ... n ]
as sql_statement [ ; ]

-- { all server | database } ：作用于服务器 | 数据库
-- event_type：create_table , drop_table , alter_table
-- event_group：作用组
-- rollback 回退

3 查看

-- 类型
exec sp_helptrigger ' table ' [ , 'type' ]
-- 文本
exec sp_helptext 'trigger_name'

4 修改

同理create。

alter trigger trigger_name

5 使有效

disable | enable trigger { [ schema . ] trigger_name [ , ... n ] | all }
on object_name

-- schema. ：架构
-- object_name：在其上创建DML触发器的对象名称

6 删除

drop trigger trigger_name

---

十、备份和还原

1 定义

2 创建备份

3 还原数据库